package org.languageguide.readings;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.languageguide.storage.DBConnector;
import org.languageguide.user.User;

public class FileManager {

	public static void addFile(String  readingId, String fileName, String path, User user){
		String sql = "INSERT INTO File (Id, Name, Path, UserId) Values (?, ?, ?, ?)";

		DBConnector connector = DBConnector.getInstance();
		Connection connection = connector.getConnection();
		PreparedStatement ps = null;
		try{
			ps = connection.prepareStatement(sql);
			ps.setString(1, readingId);
			ps.setString(2, readingId + ".wav");
			ps.setString(3, path);
			ps.setInt(4, user.getId());

			ps.executeUpdate();

			String updateSql = "Update Readings Set SoundFile = ? WHERE Id = ?";
			PreparedStatement ps2 = connection.prepareStatement(updateSql);
			ps2.setString(1, "/snd/readings/" + readingId + ".m4a");
			ps2.setString(2, readingId);
			ps2.executeUpdate();

			/*ResultSet rs = ps.getGeneratedKeys();
			if (rs.next()) {
				Integer id = rs.getInt(1); 
				//ps.close();
				//return id;
			} 
			else {
			    System.out.println("There are no generated keys.");
			}*/
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("msg" + e.getMessage());
		}
		finally{
			connector.close(connection);
		}

	}

	public static Integer getCategoryId(Connection connection, String shortName) throws SQLException{
		String sql = "SELECT Id From Category WHERE ShortName = ? AND Number = 0";
		PreparedStatement ps = connection.prepareStatement(sql);
		ps.setString(1, shortName);
		ResultSet rs = ps.executeQuery();
		if (rs.next()) {
			Integer id = rs.getInt("id");
			return id;
		}	
		return null;
	}
	
	public static Integer getCategoryId(String shortName) throws SQLException{
		DBConnector connector = DBConnector.getInstance();
		Connection connection = connector.getConnection();
		try{
			String sql = "SELECT Id From Category WHERE ShortName = ? AND Number = 0";
			PreparedStatement ps = connection.prepareStatement(sql);
			ps.setString(1, shortName);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				Integer id = rs.getInt("id");
				return id;
			}	
		}
		catch (SQLException e){
			e.printStackTrace();
			System.out.println("msg" + e.getMessage());
		}
		finally{
			connector.close(connection);
		}
		return null;
	}

	public static Integer getCurrentVersionNumber(Integer categoryId, String lang){
		DBConnector connector = DBConnector.getInstance();
		Connection connection = connector.getConnection();
		try{
			String sql = "Select Version From File WHERE CategoryId = ? AND Language = ?";
			PreparedStatement ps = connection.prepareStatement(sql);
			ps.setInt(1, categoryId);
			ps.setString(2, lang);
			ResultSet rs = ps.executeQuery();
			if (rs.next()){
				Integer version = rs.getInt(1);
				return version;
			}
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("msg" + e.getMessage());
		}
		finally{
			connector.close(connection);
		}
		return null;
	}	
	
	
	
	
	
	public static Integer getCategoryId(String shortName, Integer number, String language){
			String sql = "	";

			DBConnector connector = DBConnector.getInstance();
			Connection connection = connector.getConnection();
			PreparedStatement statement;
			try {
				statement = connection.prepareStatement(sql);
				//statement.setString(1, language);
				statement.setString(1, shortName);
				statement.setInt(2, number);
				
				ResultSet rs = statement.executeQuery();
				if (rs.next()){
					//Integer count = rs.getInt(1);
					Integer categoryId = rs.getInt(1);
					//ForumInfo info = new ForumInfo(categoryId, count);
					return categoryId;
				}
			} 
			catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally{
				connector.close(connection);
			}
			return null;

		}

	
	
	public static boolean hasFile(Integer categoryId, String language){
		String sql = "SELECT * FROM File WHERE CategoryId = ? AND Language = ?";
		DBConnector connector = DBConnector.getInstance();
		Connection connection = connector.getConnection();
		try {	
			PreparedStatement statement = connection.prepareStatement(sql);
			statement.setInt(1, categoryId);
			statement.setString(2, language);
			ResultSet rs = statement.executeQuery();
			if (rs.next()){
				System.out.println("has File");
				return true;	
			}
			System.out.println("no File" + categoryId + " " + language);
			return false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			connector.close(connection);
		}
		return false;
	}
	
	/**
	 * @param times
	 * @param name
	 * @param lang
	 */
	public static void setFileTimes(String times, String name, String lang){
		DBConnector connector = DBConnector.getInstance();
		Connection connection = connector.getConnection();
		try{
			Integer categoryId = getCategoryId(connection, name); 
			String sql = "UPDATE File Set Times = ? WHERE CategoryId = ? AND Language = ?";
			PreparedStatement stmt = connection.prepareStatement(sql);
			stmt.setString(1, times);
			stmt.setInt(2, categoryId);
			stmt.setString(3, lang);
			stmt.executeUpdate();
		}
		catch (Exception e){
			e.printStackTrace();
		}
		finally{
			connector.close(connection);
		}
	}

	public static boolean isLocked(Connection connection, Integer categoryId, String lang) throws SQLException{
		String sql = "SELECT Locked from CategoryImplementation WHERE CategoryId = ? AND Language = ?";
		PreparedStatement stmt = connection.prepareStatement(sql);
		stmt.setInt(1, categoryId);
		stmt.setString(2, lang);
		ResultSet rs = stmt.executeQuery();
		if (rs.next()){
			return rs.getBoolean(1);
		}
		return false;
	}
	
	public static File addPictorial(String lang, String name, User user){
		DBConnector connector = DBConnector.getInstance();
		Connection connection = connector.getConnection();
		String dirName = "/LG/uploads/pictorial/" + lang + "/";
		try {
			File directoryFile = new File(dirName); // fileName
			directoryFile.mkdirs();
			System.out.println("name" + name);
			File saveTo = new File(directoryFile, name + ".wav"); // fileName
			Integer categoryId = FileManager.getCategoryId(connection, name);
			if (isLocked(connection, categoryId, lang)){
				System.out.println("category impl locked");
				return null;
			}
			Integer versionNumber;
			if (saveTo.exists()){
				versionNumber = getCurrentVersionNumber(categoryId, lang);
				File newFile = new File(directoryFile, name + "-" + versionNumber + ".wav"); // fileName
				System.out.println("success" + saveTo + " " + newFile);		
				boolean success = saveTo.renameTo(newFile);
				if (!success) {
					copyfile(saveTo, newFile);
					saveTo.delete();
					System.out.println("file rename failed");
					//return null;
				}
				saveTo = new File(directoryFile, name + ".wav"); // this line might not be needed
			}
			else versionNumber = 0;
			String filePath = "/" + lang + "/";
			String fileHistorySql = "INSERT INTO FileHistory SELECT * FROM File WHERE Language = ? AND CategoryId = ?";
			PreparedStatement ps = connection.prepareStatement(fileHistorySql);
			ps.setString(1, lang);
			ps.setInt(2, categoryId);
			ps.executeUpdate();
			if (versionNumber == 0){
				addPictorialFile(connection, categoryId, filePath, lang,  user);
			}
			else{
				updatePictorialFile(connection, categoryId, filePath, lang,  user, versionNumber + 1 );
			}
			//Cache.flushPictorialCategorySoundFile(lang, name);
			return saveTo;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			connector.close(connection);
		}
		return null;
	}

	private static void addPictorialFile(Connection connection, Integer categoryId, String cat, String lang, User user) throws SQLException{
		String sql = "INSERT INTO File (CategoryId, Language, Name, UserId, Version) Values (?, ?, ?, ?, ?)";
		PreparedStatement ps = connection.prepareStatement(sql);
		ps.setInt(1, categoryId);
		ps.setString(2, lang);
		ps.setString(3, cat);
		ps.setInt(4, user.getId());
		ps.setInt(5, 1);
		ps.executeUpdate();
	}

	private static void updatePictorialFile(Connection connection, Integer categoryId, String filePath, String lang, User user, Integer versionNumber) throws SQLException{
		String sql = "UPDATE File Set Version= ?, UserId = ? WHERE CategoryId =? AND Language=?";
		PreparedStatement ps = connection.prepareStatement(sql);
		ps.setInt(1, versionNumber);
		ps.setInt(2, user.getId());
		ps.setInt(3, categoryId);
		ps.setString(4, lang);
		ps.executeUpdate();
	}


	private static void copyfile(File f1, File f2){
		try{
			InputStream in = new FileInputStream(f1);

			//For Append the file.
			//  OutputStream out = new FileOutputStream(f2,true);

			//For Overwrite the file.
			OutputStream out = new FileOutputStream(f2);

			byte[] buf = new byte[1024];
			int len;
			while ((len = in.read(buf)) > 0){
				out.write(buf, 0, len);
			}
			in.close();
			out.close();
			System.out.println("File copied.");
		}
		catch(FileNotFoundException ex){
			System.out.println(ex.getMessage() + " in the specified directory.");
			System.exit(0);
		}
		catch(IOException e){
			System.out.println(e.getMessage());  
		}


	}
}